﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[usp_InsumoHijosConsultar]
	@idParte int
AS
BEGIN
	DECLARE @CurrentDespiece hierarchyid

	SELECT @CurrentDespiece = Jerarquia
	FROM Insumo
	WHERE IdParte = @idParte and Nivel=1

	SELECT D.IdParte, P.Descripcion, Jerarquia.ToString() as Jerarquia, Nivel, Cantidad, (select idparte from Insumo where Jerarquia=d.Jerarquia.GetAncestor(1) ) as IdPartePadre
	FROM Insumo D
	Inner join Parte P on D.IdParte = P.IdParte
	WHERE Jerarquia.IsDescendantOf(@CurrentDespiece) = 1
	order by  Jerarquia desc
END